﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class SupportingMaterialDao
    {
        private readonly SqlConnection _sqlConnection;

        public SupportingMaterialDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        public SupportingMaterialDao(string sqlConnection)
        {
            _sqlConnection = new SqlConnection(sqlConnection);
        }

        #region Sql Command
        private const string SqlMarkActivated = @"
                                UPDATE SupportingMaterial SET IsActive = @isActive WHERE Id = @id";
        private const string QueryStringSelectAllSupportingMaterial = @"
                                SELECT Id, Url, [Description], [Type], AddedBy, AddedDate, OrgID,
                                Case(IsActive) when 1 then 'Yes' else 'No' END AS IsActive
                                FROM SupportingMaterial WHERE OrgID=@orgId";
        private const string QueryStringSelectAllSupportingMaterialById = @"
                                SELECT S.Id, Url, [Description], [Type], AddedBy, AddedDate, S.IsActive, S.OrgId
                                FROM SupportingMaterial S, Organisations O WHERE
                                O.ID = S.OrgID AND S.Id = @id";
        private const string QueryStringInsertSupportingMaterial = @"
                                INSERT INTO SupportingMaterial(Url, [Description], [Type], AddedBy,
                                AddedDate, UserName, IsActive, OrgId) VALUES(@url, @des, @type, @addedBy,
                                @addedDate, @username, 'True', @orgId)";
        private const string QueryStringUpdateSupportingMaterial = @"
                                UPDATE SupportingMaterial SET Url=@url, [Description]=@des,
                                [Type]=@type WHERE Id=@id";
        private const string QueryStringSelectOrgName = @"SELECT ID, Name FROM Organisations";
        private const string QueryStringSelectOrgNameByMaterialId = @"
                                SELECT [Name] FROM Organisations O, SupportingMaterial S 
                                WHERE O.ID = S.OrgID AND S.Id = @id";
        #endregion

        /// <summary>
        /// Marks the activated.
        /// </summary>
        /// <param name="isActive">if set to <c>true</c> [is active].</param>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public int MarkActivated(bool isActive, int id)
        {
            try
            {
                var sqlParam = new SqlParameter[2];
                sqlParam[0] = new SqlParameter("@isActive", SqlDbType.Bit) { Value =  isActive};
                sqlParam[1] = new SqlParameter("@id", SqlDbType.Int) { Value = id };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlMarkActivated, sqlParam);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                throw;
            }
        }

        /// <summary>
        /// Selects all supporting material.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectAllSupportingMaterialByOrgId(int orgId)
        {
            var param = new SqlParameter("@orgId", SqlDbType.Int) { Value = orgId };
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectAllSupportingMaterial, param);
            return ds.Tables[0];
        }

        /// <summary>
        /// Selects the supporting material by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public SupportingMaterial SelectSupportingMaterialById(int id)
        {
            var param = new SqlParameter("@id", SqlDbType.Int) { Value = id };
            var dataRow =
                SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectAllSupportingMaterialById, param).Tables[0].
                    Rows[0];
            return new SupportingMaterial
            {
                Url = (string)dataRow["Url"],
                Description = (string)dataRow["Description"],
                AddedBy = (string)dataRow["AddedBy"],
                AddedDate = (DateTime)dataRow["AddedDate"],
                Type = (string)dataRow["Type"],
                OrgId = (int)dataRow["OrgId"],
                Id = (int)dataRow["Id"]
            };
        }

        /// <summary>
        /// Inserts the supporting material.
        /// </summary>
        /// <param name="sm">The sm.</param>
        /// <returns></returns>
        public int InsertSupportingMaterial(SupportingMaterial sm)
        {
            try
            {
                var param = new SqlParameter[7];
                param[0] = new SqlParameter("@url", SqlDbType.VarChar, 500) { Value = sm.Url };
                param[1] = new SqlParameter("@des", SqlDbType.NText) { Value = sm.Description };
                param[2] = new SqlParameter("@type", SqlDbType.VarChar, 20) { Value = sm.Type };
                param[3] = new SqlParameter("@addedBy", SqlDbType.NVarChar, 100) { Value = sm.AddedBy };
                param[4] = new SqlParameter("@addedDate", SqlDbType.DateTime) { Value = sm.AddedDate };
                param[5] = new SqlParameter("@username", SqlDbType.VarChar, 100) { Value = sm.UserName };
                param[6] = new SqlParameter("@orgId", SqlDbType.Int) { Value = sm.OrgId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, QueryStringInsertSupportingMaterial, param);
            }
            catch (ApplicationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                return -1;
            }
        }

        /// <summary>
        /// Updates the supporting material.
        /// </summary>
        /// <param name="sm">The sm.</param>
        /// <returns></returns>
        public int UpdateSupportingMaterial(SupportingMaterial sm)
        {
            try
            {
                var param = new SqlParameter[4];
                param[0] = new SqlParameter("@url", SqlDbType.VarChar, 500) { Value = sm.Url };
                param[1] = new SqlParameter("@des", SqlDbType.NText) { Value = sm.Description };
                param[2] = new SqlParameter("@type", SqlDbType.VarChar, 20) { Value = sm.Type };
                param[3] = new SqlParameter("@id", SqlDbType.Int) { Value = sm.Id };
                return
                    SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text,
                                                            QueryStringUpdateSupportingMaterial, param);
            }
            catch (ApplicationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                return -1;
            }
        }

        public string SelectedItemOrganisationName(int materialId)
        {
            try
            {
                var param = new SqlParameter("@id", SqlDbType.Int) {Value = materialId};
                var result = SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, QueryStringSelectOrgNameByMaterialId,
                                                param);
                return result.ToString();
            }
            catch (ApplicationException e)
            {
                ExceptionHandler.WriteErrorLog(e);
                return null;
            }
        }

        /// <summary>
        /// Selects the name of the org.
        /// </summary>
        /// <returns></returns>
        public DataTable SelectOrgName()
        {
            var ds = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, QueryStringSelectOrgName);
            return ds.Tables[0];
        }
    }
}
